package com.hns.can.master.component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hns.can.master.entity.BaseStnInfoEntity;
import com.hns.frame.enumrate.MsgType;
import com.hns.frame.exception.BusinessException;
import com.hns.tool.pub.PubUtil;

/**
 * @版权：HNS 版权所有 (c) 2013
 * @author:JeffXu
 * @version Revision 1.0.0
 * @email:835264047@qq.com
 * @see:
 * @创建日期：2014-8-14
 * @功能说明：解析站点经纬度excel文件成list对象
 * @begin
 * @修改记录:
 * @修改后版本          修改人      	修改内容
 * @2014-8-14  	         JeffXu        	创建
 * @end
 */
public class ParseStnLgtLttExcel {
    
    /**
     * @param file
     * @return
     * @throws IOException
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：提供对外访问的方法
     */
    public static List<BaseStnInfoEntity> parseExcelToList(File file) throws IOException {
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) { // 读取 office 2003 excel
            wb = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equals(extension)) { // 读取Office 2007 excel
            wb = new XSSFWorkbook(new FileInputStream(file));
        } else {
            file.delete();
            throw new BusinessException(MsgType.ERROR.getFlag(), "不支持的文件类型");
        }
        
        sheet = wb.getSheetAt(0);
//        if (!"站点经纬度信息".equals(sheet.getSheetName())) {
//            file.delete();
//            throw new BusinessException(MsgType.ERROR.getFlag(), "您导入的模版有误。");
//        }
        
        List<BaseStnInfoEntity> list = new ArrayList<BaseStnInfoEntity>();
        Map<Integer,String> map = new HashMap<Integer,String>();
        int begin = sheet.getFirstRowNum();
        int end = sheet.getLastRowNum();
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            System.out.print(row);
            if (row == null) {
                continue;
            }
            row = sheet.getRow(i);
            BaseStnInfoEntity entity = new BaseStnInfoEntity();
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            for(int j = 0;j<cells;j++){
                cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                if( i == 0){
                    switch (j) {
                        case 0:
                            map.put(0,cellValue);
                            break;
                        case 1:
                            map.put(1,cellValue);
                            break;
                        case 2:
                            map.put(2,cellValue);
                            break;
                        case 3:
                            map.put(3,cellValue);
                            break;
                        case 4:
                            map.put(4,cellValue);
                            break;
                        case 5:
                            map.put(5,cellValue);
                            break;
                        case 6:
                            map.put(6,cellValue);
                            break;
                        case 7:
                            map.put(7,cellValue);
                            break;
                        case 8:
                            map.put(8,cellValue);
                            break;
                        case 9:
                            map.put(9,cellValue);
                            break;
                        case 10:
                            map.put(10,cellValue);
                            break;
                        case 11:
                            map.put(11,cellValue);
                            break;
                    }
                }else {
                    switch (map.get(j)) {
                        case "站点编号":
                            entity.setStnCd(cellValue);
                            break;
                        case "站点名称":
                            entity.setStnName(cellValue);
                            break;
                        case "站点别名":
                            entity.setStnAlias(cellValue);
                            break;
                        case "方向":
                            if ("上行".equals(cellValue)) {
                                entity.setDirection("LIT00001");
                            } else if ("下行".equals(cellValue)) {
                                entity.setDirection("LIT00002");
                            } else if ("环形".equals(cellValue)) {
                                entity.setDirection("LIT00003");
                            } else if ("".equals(cellValue)) {
                                entity.setDirection(null);
                            } else {
                                entity.setDirection("LIT00004");
                            }
                            break;
                        case "经度":
                            if (PubUtil.isEmpty(cellValue)) {
                                entity.setLgt(null);
                            } else {
                                entity.setLgt(new BigDecimal(cellValue));
                            }
                            break;
                        case "纬度":
                            if (PubUtil.isEmpty(cellValue)) {
                                entity.setLtt(null);
                            } else {
                                entity.setLtt(new BigDecimal(cellValue));
                            }
                            break;
                        case "进站半径":
                            if (PubUtil.isNotEmpty(cellValue)) {
                                entity.setInRadius(new BigDecimal(cellValue));
                            }
                            break;
                        case "出站半径":
                            if (PubUtil.isNotEmpty(cellValue)) {
                                entity.setOutRadius(new BigDecimal(cellValue));
                            }
                            break;
                        case "限速":
                            if (PubUtil.isNotEmpty(cellValue)) {
                                entity.setSpeedLimit(new BigDecimal(cellValue));
                            }
                            break;
                        case "所在道路":
                            entity.setRoad(cellValue);
                            break;
                        case "位置信息":
                            entity.setLoDetailInfo(cellValue);
                            break;
                        case "备注":
                            entity.setRemark(cellValue);
                            break;
                    }
                }
            }
            if(i!=0){
                if(PubUtil.isEmpty(entity.getStnCd()) && PubUtil.isEmpty(entity.getStnName()) && PubUtil.isEmpty(entity.getDirection())
             && PubUtil.isEmpty(entity.getLtt()) && PubUtil.isEmpty(entity.getLoDetailInfo()) && PubUtil.isEmpty(entity.getLgt())
             && PubUtil.isEmpty(entity.getRemark()) && PubUtil.isEmpty(entity.getOutRadius()) && PubUtil.isEmpty(entity.getSpeedLimit())){
                continue;
            }
                list.add(entity);
            }
        }
        return list;
    }
    
    /**
     * @param cell
     * @return
     * @author:JeffXu
     * @email:835264047@qq.com
     * @创建日期:2014-8-14
     * @功能说明：得到每个表格的值
     */
    private static String getCellValue(Cell cell) {
        String value = null;
        DecimalFormat df = new DecimalFormat("0");// 格式化 number String // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("###.######");// 格式化数字
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = nf.format(cell.getNumericCellValue());
                    } else {
//                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                        Double d = cell.getNumericCellValue();

                        value = nf.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = cell.toString();
            }
        }else{
            value = "";
        }
        return value;
    }
    
    public static boolean isNum(String cellValue){
    	if(cellValue.indexOf(".") == -1){
    		return false;
    	}
    	String a = cellValue.substring(cellValue.indexOf(".")+1,cellValue.length());
    	if(a.length()!=6){
    		return false;
    	}
    	return true;
    }
    
    public static void main(String[] args) throws IOException {
        File file = new File("x:\\statLgtLtt.xls");
        List<BaseStnInfoEntity> list = parseExcelToList(file);
        System.out.println(list.size());
        System.out.println(list);
    }
}
